package kr.co.boogui.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import kr.co.boogui.model.JoinReservationVO;
import kr.co.boogui.model.MovieVO;
import kr.co.boogui.model.ParamVO;
import mybatis3.helper.MVCSqlSessionFactory;

import org.apache.ibatis.session.SqlSession;

public class ReservationDAO {
	public static List<MovieVO> readReservationInfo(String chartmn, String mno) {
		List<MovieVO> result = null;
		SqlSession sess = null;
		ParamVO pvo = new ParamVO();
		pvo.setChartmn(chartmn);
		pvo.setMno(mno);
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.ReservationInfoSQL",
					pvo);
		} finally {
			sess.close();
		}
		return result;
	}

	public static JoinReservationVO readReservationdetailInfo(String rno,
			String mno) {
		ParamVO rvo = new ParamVO();
		SqlSession sess = null;
		JoinReservationVO result = null;
		rvo.setRno(rno);
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectOne(
					"ReservationMapper.ReservationdetailInfoSQL", rvo);
		} finally {
			sess.close();
		}
		return result;
	}

	public static List<MovieVO> readReservationdetail(String chartmn, String mno) {
		List<MovieVO> result = null;
		SqlSession sess = null;
		ParamVO pvo = new ParamVO();
		pvo.setChartmn(chartmn);
		pvo.setMno(mno);
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.ReservationdetailSQL",
					pvo);
		} finally {
			sess.close();
		}
		return result;
	}

	public static List<MovieVO> isPlay() {
		List<MovieVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.isPlaymovSQL");

		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<ParamVO> playCineLoc(MovieVO m) {
		List<ParamVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.playCineLocSQL", m);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<ParamVO> playCineName(MovieVO m) {
		List<ParamVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.playCinenameSQL", m);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<ParamVO> playDate(ParamVO p) {
		List<ParamVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.playDateSQL", p);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<ParamVO> playTime(ParamVO p) {
		List<ParamVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.playTimeSQL", p);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static MovieVO movieTitle(String movno) {
		MovieVO result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectOne("ReservationMapper.movieTitleSQL", movno);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<String> selectSeat(ParamVO p) {
		final String DRV = "org.mariadb.jdbc.Driver";
		final String URL = "jdbc:mariadb://112.216.253.122:3306/jwj8508?user=jwj8508&password=jwj8508";
		/*final String DRV = "com.mysql.jdbc.Driver";
		final String URL = "jdbc:mysql://jiinn.cafe24.com:3306/jiinn?user=jiinn&password=qpalzm00";*/
		String selectSeatSQL = "select * from seatinfo i inner join showing s on i.shno = s.shno"
				+ " inner join movie m on s.movno = m.movno"
				+ " inner join cinema c on s.cno = c.cno"
				+ " where m.movno = ? and c.cno = ? and shdate = ? and shtime = ?"; 
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> result = new ArrayList<>();

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL);;
			pstmt = conn.prepareStatement(selectSeatSQL);			
			pstmt.setInt(1, p.getMovno());
			pstmt.setString(2, p.getCno());
			pstmt.setString(3, p.getShdate());
			pstmt.setString(4, p.getShtime());
			rs = pstmt.executeQuery();
			while (rs.next()) {				
				for (int i = 1; i <= 181; i++) {
					result.add(rs.getString(i));
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)try {rs.close();rs = null;} catch (Exception e) {}
			if (pstmt != null)try {pstmt.close();pstmt = null;} catch (Exception e) {}
			if (conn != null)try {conn.close();	conn = null;} catch (Exception e) {}
		}
		return result;
	}
	
	public static boolean updateSeat(String sql) {
		final String DRV = "org.mariadb.jdbc.Driver";
		final String URL = "jdbc:mariadb://112.216.253.122:3306/jwj8508?user=jwj8508&password=jwj8508";
		/*final String DRV = "com.mysql.jdbc.Driver";
		final String URL = "jdbc:mysql://jiinn.cafe24.com:3306/jiinn?user=jiinn&password=qpalzm00";*/
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL);;
			pstmt = conn.prepareStatement(sql);			
			int r = pstmt.executeUpdate();
			if (r>0) result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null)try {pstmt.close();pstmt = null;} catch (Exception e) {}
			if (conn != null)try {conn.close();	conn = null;} catch (Exception e) {}
		}
		return result;
	}
	
	public static ParamVO selectShinfo(String shno) {
		ParamVO result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectOne("ReservationMapper.shinfoSQL", shno);
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static List<String> seats(ParamVO p) {
		List<String> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.seatSQL", p);
		} finally {
			sess.close();
		}
		return result;
	}

	public static boolean insertReservation(ParamVO p) {
		boolean result = false;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			int r = sess.insert("ReservationMapper.insertRsrvSQL", p);
			if (r>0) result = true;
			sess.commit();
		} finally {
			sess.close();
		}
		return result;		
	}
	
	public static String selectRno() {
		String result = "";
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectOne("ReservationMapper.selectRnoSQL");
		} finally {
			sess.close();
		}
		return result;
	}
	
	public static boolean insertSales(ParamVO p) {
		boolean result = false;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			int r = sess.insert("ReservationMapper.insertSalesSQL", p);
			if (r>0) result = true;
			sess.commit();
		} finally {
			sess.close();
		}
		return result;		
	}
	
	public static List<ParamVO> selectIspay() {
		List<ParamVO> result = null;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectList("ReservationMapper.selectIspaySQL");
		} finally {
			sess.close();
		}
		return result;	
	}
	
	public static int selectPayCnt() {
		int result = 0;
		SqlSession sess = null;
		try {
			sess = MVCSqlSessionFactory.openSession();
			result = sess.selectOne("ReservationMapper.selectPayCntSQL");
		} finally {
			sess.close();
		}
		return result;
	}
	
}